import { execute, select } from '../../utils/db'
import { Page, PageReq } from '../../types/page'
import { TrainerCourse } from '../../types/trainerCourse'

export class TrainerCourseDao {
    /**
   * 创建训练人员课程
   * :  
   */
  static async create(trainerCourse: Omit<TrainerCourse, 'id'> ): Promise<number> {
    const result = await execute(
      'INSERT INTO trainer_course (course_id, slot_id, trainer_id) VALUES (?, ?, ?)',
      [trainerCourse.course_id, trainerCourse.slot_id, trainerCourse.trainer_id]
    )
    return result?.lastInsertId
  }

  /**
   * 根据ID获取训练人员课程
   */
  static async getById(id: number): Promise<TrainerCourse | null> {
    const result = await select<TrainerCourse>(
      'SELECT * FROM trainer_course WHERE ID = ? LIMIT 1',
      [id]
    )
    return result[0]
  }

  /**
   * 根据course_id统计训练人数
   * @param course_id
   * @returns 
   */
  static async countTrainerByCourseId(course_id : number): Promise<number> {
    const result = await select<TrainerCourse>(
      'SELECT count(1) as total FROM trainer_course WHERE course_id = ?',
      [course_id]
    )
    return result[0].total
  }

    /**
   * 删除排课课程
   */
  static async delete(id: number): Promise<void> {
    await execute('DELETE FROM trainer_course WHERE id = ?', [id])
  }

  static async list( trainerCourse: TrainerCourse): Promise<TrainerCourse[]> {

    const fields = []
    const values = []

    if (trainerCourse.course_id) {
      fields.push('course_id = ?')
      values.push(trainerCourse.course_id)
    }
    if (trainerCourse.slot_id) {
      fields.push('slot_id = ?')
      values.push(trainerCourse.slot_id)
    }
    if (trainerCourse.trainer_id) {
      fields.push('trainer_id = ?')
      values.push(trainerCourse.trainer_id)
    }
    const condition = fields.length > 0 ? `WHERE ${fields.join(' AND ')}` : ''
    const result = await select<TrainerCourse[]>(
      `SELECT * FROM trainer_course ${condition}`,
      values
    )
    return result
  }

  /**
   * 分页查询训练人员课程
   */
  static async page(params: PageReq<TrainerCourse>): Promise<Page<TrainerCourse>> {
    const { pageNum, pageSize } = params
    const offset = (pageNum - 1) * pageSize
    const trainerCourse = params.params
    
    const fields = []
    const values = []

    if (trainerCourse.course_id) {
      fields.push('course_id = ?')
      values.push(trainerCourse.course_id)
    }
    if (trainerCourse.slot_id) {
      fields.push('slot_id = ?')
      values.push(trainerCourse.slot_id)
    }
    if (trainerCourse.trainer_id) {
      fields.push('trainer_id = ?')
      values.push(trainerCourse.trainer_id)
    }

    const condition = fields.length > 0 ? `WHERE ${fields.join(' AND ')}` : ''
    const [totalResult, listResult] = await Promise.all([
      select('SELECT COUNT(*) as count FROM trainer_course ' + condition, values),
      select(`SELECT * FROM trainer_course ${condition} LIMIT ${pageSize} OFFSET ${offset}`, values)
    ])

    return {
      total: totalResult[0].count || 0,
      list: (listResult as unknown as TrainerCourse[]) || []
    }
  }
}